
<?php
 
// error_reporting(E_ALL);
// ini_set("display_errors", 1);
// ini_set('display_startup_errors', 1);
// error_reporting(-1);

 
 include_once "../common/base.php";
 require_once('lib/PHPMailerAutoload.php');
 require_once('lib/google-api-php-client-master/autoload.php');
 
class sender
{
    private $_db;
	private $smtp_info = array();
	private $msg_cnt = array();
	private $delay_msg_cnt = array();
	private $mailer = array();
	private $sender = array();
	private $delay_sender = array();
	private $log_level;
	private $client;
	
    public function __construct($db=NULL)
    {
        if(is_object($db))
        {
            $this->_db = $db;
        }
        else
        {
            $dsn = "mysql:host=".DB_HOST.";dbname=".DB_NAME;
            $this->_db = new PDO($dsn, DB_USER, DB_PASS);
        }
        $this->client = new Google_Client();
	}
	
	public function init(){
        $this->log_level = 2;
		$this->listSmtp();
		$this->countCampaignMessage();
		$this->countCampaignDelayMessage();
		$this->mailer = new PHPMailer;
		$this->listSender();
		$this->listDelaySender();	
		$this->gmail_client();
	}
	
	public function listSmtp(){
        $sql = "SELECT *
                FROM smtp_info";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()==0) 
			{
                echo "Empty smtp_info table.";
            }
			else
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					$this->smtp_info[$row['smtp_id']] = $row;
				}
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
   	}


 public function updateWorked(){
        $sql = "UPDATE alias_mail
                SET worked=1";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
        }

$sql = "UPDATE sub_master_mail
                SET worked=1";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
        }

        }

	
	public function listAttachments(){
		$attachments = array();
		$sql = "SELECT *
                FROM message_attachment";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					if(!isset($attachments["{$row['campaign_id']}"])) $attachments["{$row['campaign_id']}"] = array();
					if(!isset($attachments["{$row['campaign_id']}"]["{$row['message_id']}"])) $attachments["{$row['campaign_id']}"]["{$row['message_id']}"] = array();
					array_push($attachments["{$row['campaign_id']}"]["{$row['message_id']}"], $row['path']);
				}
            }
			else
			{
				//echo "No attachments";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
		return $attachments;
		
	}
	
	public function listSender(){
		$sql = "SELECT *
                FROM message";
		$attachments = $this->listAttachments();
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					$this->sender[$row['campaign_id']][$row['message_id']]['message'] = $row['message'];
					$this->sender[$row['campaign_id']][$row['message_id']]['isHTML'] = $row['isHTML'];
					if( isset($attachments["{$row['campaign_id']}"]) && isset($attachments["{$row['campaign_id']}"]["{$row['message_id']}"]))
						$this->sender[$row['campaign_id']][$row['message_id']]['attachments'] = $attachments["{$row['campaign_id']}"]["{$row['message_id']}"];
				}
            }
			else
			{
				//echo "No messages defined in any campaign.";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
		
		//var_dump($this->sender);
	}
	
	public function listDelayAttachments(){
		$attachments = array();
		$sql = "SELECT *
                FROM delay_message_attachment";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					if(!isset($attachments["{$row['campaign_id']}"])) $attachments["{$row['campaign_id']}"] = array();
					if(!isset($attachments["{$row['campaign_id']}"]["{$row['delay_message_id']}"])) $attachments["{$row['campaign_id']}"]["{$row['delay_message_id']}"] = array();
					array_push($attachments["{$row['campaign_id']}"]["{$row['delay_message_id']}"], $row['path']);
				}
            }
			else
			{
				//echo "No attachement in dealy messsage.";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
		return $attachments;
		
	}

	public function listDelaySender(){
		
		$lastMail = "";
		$attachments = $this->listDelayAttachments();
		$sql = "SELECT A.delay_message_id, A.campaign_id, B.message_id, A.delay_message, A.isHTML, A.delay FROM delay_message as A LEFT JOIN message as B ON A.campaign_id = B.campaign_id AND A.delay_message_id = B.message_id ORDER BY A.delay_message_id";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					$this->delay_sender[$row['campaign_id']][$row['delay_message_id']]['message'] = $row['delay_message'];
					$this->delay_sender[$row['campaign_id']][$row['delay_message_id']]['isHTML'] = $row['isHTML'];
					if( isset($attachments["{$row['campaign_id']}"]) && isset($attachments["{$row['campaign_id']}"]["{$row['delay_message_id']}"]))
						$this->delay_sender[$row['campaign_id']][$row['delay_message_id']]['attachments'] = $attachments["{$row['campaign_id']}"]["{$row['delay_message_id']}"];
				}
            }
			else
			{
				//echo "No messages defined in any campaign.";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
		
		//var_dump($this->delay_sender);
	}
	
	public function countCampaignMessage(){
		$sql = "SELECT campaign_id, COUNT(message_id) AS theCount
                FROM message GROUP BY campaign_id;";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					$this->msg_cnt[$row['campaign_id']] = $row['theCount'];
				}
            }
			else
			{
				//echo "No messages defined in any campaign.";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
	}
	
	public function countCampaignDelayMessage(){
		$sql = "SELECT campaign_id, COUNT(delay_message_id) AS theCount
                FROM delay_message GROUP BY campaign_id;";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					$this->delay_msg_cnt[$row['campaign_id']] = $row['theCount'];
				}
            }
			else
			{
				//echo "No delay messages defined in any campaign.";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
	}
	
	public function deleteUnnecessarydata(){
	    //count campaings
	    $campaigns = 0;
	    
	    $sql = "SELECT COUNT(campaign_id) AS theCount
                FROM campaign;";
	    if($stmt = $this->_db->prepare($sql)) {
		$stmt->execute();
		$row = $stmt->fetch();
		$campaigns = $row['theCount'];
	    }
	    
	    for($campaign_id=1;$campaign_id<=$campaigns;$campaign_id++){
		//delete from send_mail
		if(isset($this->msg_cnt[$campaign_id])){
		  $msg_count = $this->msg_cnt[$campaign_id];
		  $sql = "DELETE FROM send_mail WHERE campaign_id=:campaign_id AND count>:msg_count;";
		  if($stmt = $this->_db->prepare($sql)) {
			$stmt->bindParam(":campaign_id", $campaign_id, PDO::PARAM_INT);
			$stmt->bindParam(":msg_count", $msg_count, PDO::PARAM_INT);
			$stmt->execute();
		  }
		}
	    }
	    
	    //delete from send_delay_mai
	    for($campaign_id=1;$campaign_id<=$campaigns;$campaign_id++){
		if(isset($this->delay_msg_cnt[$campaign_id])){
		    $msg_count = $this->delay_msg_cnt[$campaign_id];
		    $sql = "DELETE FROM send_delay_mail WHERE campaign_id=:campaign_id AND count>:msg_count;";
		    if($stmt = $this->_db->prepare($sql)) {
			  $stmt->bindParam(":campaign_id", $campaign_id, PDO::PARAM_INT);
			  $stmt->bindParam(":msg_count", $msg_count, PDO::PARAM_INT);
			  $stmt->execute();
		    }
		}
	    }
	}
	
	public function getMailsToSend($table_name, $interval){
		$mails = array();
		$time = date("Y-m-d H:i:s", strtotime("+{$interval} minutes", time()));
		
		$trans_id = rand(1000,9999);
		
		//Fetch
		if($table_name == 'send_mail'){
		$sql = "UPDATE send_mail SET trans_id = :trans_id
				WHERE
				STR_TO_DATE(fire,'%Y-%m-%d %H:%i:%s') < STR_TO_DATE(:time,'%Y-%m-%d %H:%i:%s') AND
				isFired = 0 AND
				sender IN (SELECT mail_id from alias_mail where worked = 1) AND trans_id = 0 LIMIT 100;
				";
		}
		else {
			$sql = "UPDATE send_delay_mail SET trans_id = :trans_id
				WHERE
				STR_TO_DATE(fire,'%Y-%m-%d %H:%i:%s') < STR_TO_DATE(:time,'%Y-%m-%d %H:%i:%s') AND
				isFired = 0 AND
                sender IN (SELECT mail_id from alias_mail where worked = 1) AND trans_id = 0 LIMIT 100;
				";
		}
		if($stmt = $this->_db->prepare($sql)) {
            //$stmt->bindParam(":table_name", $table_name, PDO::PARAM_STR);
            $stmt->bindParam(":time", $time, PDO::PARAM_STR);
            $stmt->bindParam(":trans_id", $trans_id, PDO::PARAM_INT);
            $stmt->execute();
        }
        else
        {
            echo "Error! Sorry, Error in SQL.";
        }

        
		if($table_name == 'send_mail'){
        $sql = "SELECT * FROM send_mail
                WHERE
                trans_id = :trans_id;
                ";
        }
        else {
            $sql = "SELECT * FROM send_delay_mail
                WHERE
                trans_id = :trans_id;
                ";
        }
        if($stmt = $this->_db->prepare($sql)) {
			$stmt->bindParam(":trans_id", $trans_id, PDO::PARAM_INT);
            $stmt->execute();
            if($stmt->rowCount()!=0) 
			{
				for($i=0 ; $i<$stmt->rowCount() ; $i++){
    				$row = $stmt->fetch();
					array_push($mails, $row);
				}
            }
			else
			{
				//echo "No messages defined in any campaign.";
			}
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }
		
		//UPDATE
		if($table_name == 'send_mail'){
			$sql = "UPDATE send_mail
					SET isFired = 1, trans_id = 0
					WHERE
                        trans_id = :trans_id;
					";
		}
		else {
			$sql = "UPDATE send_delay_mail
					SET isFired = 1, trans_id = 0
					WHERE
                        trans_id = :trans_id;
					";			
		}
        if($stmt = $this->_db->prepare($sql)) {
			//$stmt->bindParam(":table_name", $table_name, PDO::PARAM_STR);
            $stmt->bindParam(":trans_id", $trans_id, PDO::PARAM_INT);
            $stmt->execute();
		}
		else
		{
        	echo "Error! Sorry, Error in SQL.";
        }

		return $mails;	
	}
	
	public function doJob(){
		//delete from table where count = count(message)+1 for each from
		
		
		//Get the mails of next 2 mins with isFired = 0
		$interval = 2;
		$send_mails = $this->getMailsToSend("send_mail" , $interval);
		$send_delay_mails = $this->getMailsToSend("send_dealy_mail", $interval);
		
		//send the mails
		$this->send_mail($send_mails, "normal");
		$this->send_mail($send_delay_mails, "delay");
    } 
   
    public function getAliasInfo($mail_id){
	    $sql = "SELECT *
                FROM alias_mail WHERE mail_id=:mail_id";
        if($stmt = $this->_db->prepare($sql)) {
			$stmt->bindParam(":mail_id", $mail_id, PDO::PARAM_STR);
            $stmt->execute();
    		$row = $stmt->fetch();
			return $row;
		}
		else
		{
        	echo "No alias_mail info found, this should not happen<br/>";
        }
   }
   
    public function refreshToken(&$old_access_token, $mail_id){
        try {
            $this->client->setAccessToken($old_access_token);
            if($this->client->isAccessTokenExpired()){
                if($this->log_level > 1) echo "User token expired.". "<br/>";
                if($this->log_level > 1) echo "Old Access token:" . $old_access_token . "<br/>";
                $google_token = $this->getRefreshTokenFromDB($mail_id);
                
                $this->client->refreshToken($google_token);
                $access_token = $this->client->getAccessToken();
                if($this->log_level > 1) echo "New Access token:" . $access_token . "<br/>";
                
                $sql = "UPDATE alias_mail SET password=:access_token WHERE mail_id=:mail_id";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":mail_id", $mail_id, PDO::PARAM_STR);
                    $stmt->bindParam(":access_token", $access_token, PDO::PARAM_STR);
                    $stmt->execute();
                }
                $sql = "UPDATE sub_master_mail SET password=:access_token WHERE mail_id=:mail_id";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":mail_id", $mail_id, PDO::PARAM_STR);
                    $stmt->bindParam(":access_token", $access_token, PDO::PARAM_STR);
                    $stmt->execute();
                }
                $old_access_token = $access_token;
            }
        }
        catch(Exception $ex){
            echo $ex->getMessage();
        }
    }

    public function getClientInfo(){
        $client_info = array ();
        $sql = "SELECT *
                FROM client_info";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->execute();
            if($stmt->rowCount()!=0) 
            {
                $row = $stmt->fetch();
                $client_info['client_id'] = $row['client_id'];
                $client_info['client_secret'] = $row['client_secret'];
                $client_info['alias_redirect'] = $row['alias_redirect'];
            }
        }
        return $client_info;
    }

    public function gmail_client(){
        $client_info = $this->getClientInfo();
        $client_id = $client_info['client_id']; //'1097530924560-9dv7lmvrmlfsdrn3rbd8a197ci34n4og.apps.googleusercontent.com';
        $client_secret = $client_info['client_secret']; //'VMTuLVpXMyjbdi_EoyeJxUnr';
        $redirect_uri = $client_info['alias_redirect']; //'http://www.bytefact.com/mesbagapi.php';

        $this->client->setClientId($client_id);
        $this->client->setClientSecret($client_secret);

        //$this->client->setRedirectUri($redirect_uri);
        //$this->client->addScope("https://mail.google.com");
    }

    public function getRefreshTokenFromDB($mail_id){
        $sql = "SELECT refresh_token
                FROM alias_mail WHERE mail_id=:mail_id";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->bindParam(":mail_id", $mail_id, PDO::PARAM_STR);
            $stmt->execute();
            $row = $stmt->fetch();
            return $row['refresh_token'];
        }
    }
    
    public function getDealyMailMessageId($from_mail, $campaign_id){
        $sql = "SELECT message_id
                FROM send_delay_mail WHERE from_mail=:from_mail AND campaign_id=:campaign_id;";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->bindParam(":from_mail", $from_mail, PDO::PARAM_STR);
            $stmt->bindParam(":campaign_id", $campaign_id, PDO::PARAM_INT);
            $stmt->execute();
            $row = $stmt->fetch();
            return $row['message_id'];
        }
        return $this->delay_msg_cnt[$campaign_id];
    }
    
        function stri_replace( $find, $replace, $string ) {
        //Case-insensitive str_replace()
        $parts = explode( strtolower($find), strtolower($string) );
        $pos = 0;
        foreach( $parts as $key=>$part ){
            $parts[ $key ] = substr($string, $pos, strlen($part));
            $pos += strlen($part) + strlen($find);
        }

     return( join( $replace, $parts ) );
    }


    function txt2html($txt) {
    // Transforms txt in html

    //Kills double spaces and spaces inside tags.
    while( !( strpos($txt,'  ') === FALSE ) ) $txt = str_replace('  ',' ',$txt);
    $txt = str_replace(' >','>',$txt);
    $txt = str_replace('< ','<',$txt);

    //Transforms accents in html entities.
    //$txt = htmlentities($txt);

    //We need some HTML entities back!
    $txt = str_replace('"','"',$txt);
    $txt = str_replace('<','<',$txt);
    $txt = str_replace('>','>',$txt);
    $txt = str_replace('&','&',$txt);

    //Ajdusts links - anything starting with HTTP opens in a new window
    //$txt = $this->stri_replace("<a href=\"http://","<a target=\"_blank\" href=\"http://",$txt);
    //$txt = $this->stri_replace("<a href=http://","<a target=\"_blank\" href=http://",$txt);

    //Basic formatting
    $eol = ( strpos($txt,"\r") === FALSE ) ? "\n" : "\r\n";
    $html = '<p>'.str_replace("$eol$eol","</p><p>",$txt).'</p>';
    $html = str_replace("$eol","\n",$html);
    $html = str_replace("</p>","</p>\n\n",$html);
    $html = str_replace("<p></p>","<p> </p>",$html);

    //Wipes  after block tags (for when the user includes some html in the text).
    $wipebr = Array("table","tr","td","blockquote","ul","ol","li");

    for($x = 0; $x < count($wipebr); $x++) {

        $tag = $wipebr[$x];
        $html = $this->stri_replace("<$tag>
    ","<$tag>",$html);
        $html = $this->stri_replace("</$tag>
    ","</$tag>",$html);

    }

    return $html;
    }

    public function send_mail($mails, $state){
        if($this->log_level>1){
            echo "#### Mail Sending State: " . $state . "<br/>";
        }
        $appropiate_sender = array();
        $appropiate_msg_cnt = array();
        if($state == "normal"){
            $appropiate_sender = $this->sender;
            $appropiate_msg_cnt = $this->msg_cnt;
            //if($this->log==1)var_dump($this->msg_cnt);
        }
        else { 
            $appropiate_sender = $this->delay_sender;
            $appropiate_msg_cnt = $this->delay_msg_cnt;
        }
        
        
        foreach($mails as $mail){
            if(!isset($appropiate_msg_cnt[$mail['campaign_id']]) || $mail['message_id'] > $appropiate_msg_cnt[$mail['campaign_id']]) {
                  continue;
            }
            
            $From = $mail['sender'];
            
            $FromInfo = $this->getAliasInfo($From);
            $PassWord = $FromInfo['password'];
            $FromName = $FromInfo['name'];
            $ReplyTo = $From;
            $To = $mail['from_mail'];
            $to_name = $mail['from_name'];//This is actually to name.
            $MessageId = $mail['go_id'];
            if(isset($appropiate_sender[$mail['campaign_id']][$mail['message_id']]['attachments']))
                $attachments = $appropiate_sender[$mail['campaign_id']][$mail['message_id']]['attachments'];    

            $isHTML = $appropiate_sender[$mail['campaign_id']][$mail['message_id']]['isHTML'];
            $Subject = $mail['subject'];
            
            $AltBody = $appropiate_sender[$mail['campaign_id']][$mail['message_id']]['message'];
            
            
            $from_arr = explode(" ",rtrim(ltrim($to_name)));
            $fname = $from_arr[0];
            //var_dump($from_arr);
            if($to_name == $fname){
                $from_arr = explode('-',rtrim(ltrim($to_name)));
                $fname = $from_arr[0];
            }
            
            
            
            $AltBody = str_replace("{name}", $to_name, $AltBody);
            $AltBody = str_replace("{fname}", $fname, $AltBody);
            $Body = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\"
                    \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
                    <html xmlns=\"http://www.w3.org/1999/xhtml\">
                    <head>
                        <meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />
                        <title>Mail</title>
                        <!-- TemplateEndEditable -->
                        <!-- TemplateBeginEditable name=\"head\" -->
                        <!-- TemplateEndEditable -->
                    </head>
                    <body>";
            $Body = $Body . $this->txt2html($AltBody);
            $Body = $Body . "</body> </html>";
            

            $SMTPhost = $this->smtp_info[$FromInfo['smtp_id']]['smtp_host'];
            $SMTPAuth = $this->smtp_info[$FromInfo['smtp_id']]['smtp_auth'];
            $SMTPSecure = $this->smtp_info[$FromInfo['smtp_id']]['smtp_secure'];
            $SMTPPort = $this->smtp_info[$FromInfo['smtp_id']]['smtp_port'];
            $SMTPWorked = $this->smtp_info[$FromInfo['smtp_id']]['worked'];
            
            if($this->log_level>1){
                echo "## Mail Sending from:" . $From . " to:" . $To . "<br/>";
            }
        
            if($this->log_level>4){
                $this->mailer->SMTPDebug = 3;
            }
            
            $this->mailer->ClearReplyTos();
            $this->mailer->ClearAttachments();
            $this->mailer->ClearAllRecipients();
            $this->mailer->ClearCustomHeaders();
            
            $this->mailer->From = $From;
            $this->mailer->FromName = $FromName;
            $this->mailer->AddReplyTo($ReplyTo);     // Add a recipient
            $this->mailer->addAddress($To);
            $this->mailer->AddCustomHeader("In-Reply-To: ".$MessageId);
            
            if(isset($attachments))
            foreach($attachments as $path){
                if($this->log_level>3){
                    //echo "<br/>";
                    //echo $path;
                    //echo "<br/>";
                }
                $this->mailer->addAttachment($path);
            }
            if($isHTML == 1)$this->mailer->isHTML(true);
            else $this->mailer->isHTML(false);                                  // Set email format to HTML
            $this->mailer->Subject = $Subject;
            $this->mailer->Body    = $Body;
            $this->mailer->AltBody    = $AltBody;
            
            
            $failed = false;
            $gmail = "@gmail.com";
            if(strstr($From,$gmail) != false){
                try {
                    $this->refreshToken($PassWord, $From);
                    $this->client->setAccessToken($PassWord);

                    $service = new Google_Service_Gmail($this->client);

                    $this->mailer->preSend();
                    $mime = $this->mailer->getSentMIMEMessage();
                    $gmail_message = new Google_Service_Gmail_Message();
                    $data = base64_encode($mime);
                    $data = str_replace(array('+','/','='),array('-','_',''),$data); // url safe
                    $gmail_message->setRaw($data);
                    $response = $service->users_messages->send($From, $gmail_message);
                    if(!isset($response) || !isset($response['id']))
                        $failed = true;
                }
                catch(Exception $e){
                    echo $e->getMessage();
                    $failed = true;
                }
            }
            else {
                $this->mailer->Username = $From;
                $this->mailer->Password = $PassWord;                
                $this->mailer->isSMTP();
                $this->mailer->Host = $SMTPhost;
                $this->mailer->SMTPAuth = $SMTPAuth;
                $this->mailer->SMTPSecure = $SMTPSecure;
                $this->mailer->Port = $SMTPPort;
                
                if($SMTPWorked == 1){
                    $socket = fsockopen($SMTPhost, $SMTPPort, $errno, $errstr, 10); 
                    if($socket) 
                    {
                        try {
                            if(!$this->mailer->send()) $failed = true;
                        }
                        catch(Exception $ex){
                            echo $ex->getMessage();
                        }
                    }
                    else {
                        $failed = true;
                    }
                    if($failed == true){
                        $sql = "UPDATE smtp_info SET worked = 0 
                                    WHERE smtp_host=:smtp_host AND smtp_port=:smtp_port AND smtp_secure=:smtp_secure";
                        if($stmt = $this->_db->prepare($sql)) {
                            $stmt->bindParam(":smtp_host", $SMTPhost, PDO::PARAM_STR);
                            $stmt->bindParam(":smtp_port", $SMTPPort, PDO::PARAM_INT);
                            $stmt->bindParam(":smtp_secure", $SMTPSecure, PDO::PARAM_STR);
                            $stmt->execute();
                        }
                    }
                }
                else {
                    $ports = array($SMTPPort, 25, 465, 587);//, 2525, 2526);
                    $secures = array($SMTPSecure, "ssl", "tls");
                    for($i=0;$i<count($secures);$i++){
                        for($j=0;$j<count($ports);$j++){
                            $this->mailer->SMTPSecure = $secures[$i];
                            $this->mailer->Port = $ports[$j];
                            
                            echo "trying :" . $ports[$j] . "</br>";
                            echo "trying :" . $secures[$i] . "</br>";
                            
                            $socket = fsockopen($SMTPhost, $ports[$j], $errno, $errstr, 10); 
                            if($socket)
                            {
                                if(!$this->mailer->send()) $failed = true;
                                else $failed = false;
                            }
                            else {
                                $failed = true;
                            }
                            if($failed == false){
                                echo "seeting worked = 1";
                                $sql = "UPDATE smtp_info SET smtp_port=:rsmtp_port, smtp_secure=:rsmtp_secure, worked = 1 
                                            WHERE smtp_host=:smtp_host AND smtp_port=:smtp_port AND smtp_secure=:smtp_secure";
                                if($stmt = $this->_db->prepare($sql)) {
                                    $stmt->bindParam(":rsmtp_port", $ports[$j], PDO::PARAM_INT);
                                    $stmt->bindParam(":rsmtp_secure", $secures[$i], PDO::PARAM_STR);
                                    $stmt->bindParam(":smtp_host", $SMTPhost, PDO::PARAM_STR);
                                    $stmt->bindParam(":smtp_port", $SMTPPort, PDO::PARAM_INT);
                                    $stmt->bindParam(":smtp_secure", $SMTPSecure, PDO::PARAM_STR);
                                    $stmt->execute();
                                }
                                break;
                            }
                        }
                        if($j != count($ports)) break;
                    }
                }
            }
            
            if($failed) {
                echo '##########Message sending failed########.<br />';
                
                //update sending failed counter
                $total = 'total';
                $campaign = 'campaign'.$mail['campaign_id'];
                $user = $From;
                $sql = "UPDATE mails set mailsfailed=mailsfailed+1 WHERE id=:total || id=:camapign || id=:user";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":total", $total, PDO::PARAM_STR);
                    $stmt->bindParam(":camapign", $campaign, PDO::PARAM_STR);
                    $stmt->bindParam(":user", $user, PDO::PARAM_STR);
                    $stmt->execute();
                }
            
                //update alias that it didn't worked.
                $sql = "UPDATE alias_mail SET worked=0 WHERE mail_id=:mail_id";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":mail_id", $From, PDO::PARAM_STR);
                    $stmt->execute();
                }
                $sql = "UPDATE sub_master_mail SET worked=0 WHERE mail_id=:mail_id";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":mail_id", $From, PDO::PARAM_STR);
                    $stmt->execute();
                }
                
                //update mail to send it again
                if($state == "normal"){
                    $sql = "UPDATE send_mail set isFired=0 WHERE from_mail=:from";
                    if($stmt = $this->_db->prepare($sql)) {
                        $stmt->bindParam(":from", $To, PDO::PARAM_STR);
                        $stmt->execute();
                    }
                }
                else {
                    $sql = "UPDATE send_delay_mail set isFired=0 WHERE from_mail=:from";
                    if($stmt = $this->_db->prepare($sql)) {
                        $stmt->bindParam(":from", $To, PDO::PARAM_STR);
                        $stmt->execute();
                    }
                }
             }
             else {
                    $total = 'total';
                    $campaign = 'campaign'.$mail['campaign_id'];
                    $user = $From;
                    $sql = "UPDATE mails set mailssent=mailssent+1 WHERE id=:total || id=:camapign || id=:user";
                    if($stmt = $this->_db->prepare($sql)) {
                        $stmt->bindParam(":total", $total, PDO::PARAM_STR);
                        $stmt->bindParam(":camapign", $campaign, PDO::PARAM_STR);
                        $stmt->bindParam(":user", $user, PDO::PARAM_STR);
                        $stmt->execute();
                    }
                  echo '<br />Message has been sent<br />';
                  
                $sql = "UPDATE alias_mail SET worked=1 WHERE mail_id=:mail_id";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":mail_id", $From, PDO::PARAM_STR);
                    $stmt->execute();
                }
                $sql = "UPDATE sub_master_mail SET worked=1 WHERE mail_id=:mail_id";
                if($stmt = $this->_db->prepare($sql)) {
                    $stmt->bindParam(":mail_id", $From, PDO::PARAM_STR);
                    $stmt->execute();
                }

            
            //Update counter
            if($state == "normal"){
                //insert if new
                if($this->isItFirstDelay($To)){
                    //insert New
                    $count = 1;
                    //campaign_id: defined already
                    $message_id = $count;
                    $from = $To;
                    $go_id = $MessageId;
                    $campaign_id = $mail['campaign_id'];
                    $subject = $Subject;
                    $next_fire = $this->getDelayFireTimeDiff($campaign_id, $message_id);
                    $fire = date("Y-m-d H:i:s", strtotime("+{$next_fire} minutes", time()));
                    $sql = "INSERT INTO send_delay_mail(from_mail, go_id, count, campaign_id, message_id, fire, isFired, subject, sender, from_name)
                    VALUES(:from, :go_id, :count, :campaign_id, :message_id, :fire, 0, :subject, :sender, :from_name )";
                    if($stmt = $this->_db->prepare($sql)) {
                        $stmt->bindParam(":from", $from, PDO::PARAM_STR);
                        $stmt->bindParam(":from_name", $to_name, PDO::PARAM_STR);
                        $stmt->bindParam(":go_id", $go_id, PDO::PARAM_STR);
                        $stmt->bindParam(":count", $count, PDO::PARAM_INT);
                        $stmt->bindParam(":campaign_id", $campaign_id, PDO::PARAM_INT);
                        $stmt->bindParam(":message_id", $message_id, PDO::PARAM_INT);
                        $stmt->bindParam(":fire", $fire, PDO::PARAM_STR);
                        $stmt->bindParam(":subject", $subject, PDO::PARAM_STR);
                        $stmt->bindParam(":sender", $From, PDO::PARAM_STR); 
                        $stmt->execute();
                        $stmt->closeCursor();
            
                        $userID = $this->_db->lastInsertId();
                        $succeed = dechex($userID);
                    } else {
                        echo "error in INSERT send_mail table for first mail fire insert";
                    }
                }
                else {
                //update time
                    $from = $To;//bug
                    $message_id = $this->getDealyMailMessageId($To, $mail['campaign_id']);
                    $campaign_id = $mail['campaign_id'];
                    $next_fire = $this->getDelayFireTimeDiff($campaign_id, $message_id);
                    $fire = date("Y-m-d H:i:s", strtotime("+{$next_fire} minutes", time()));
                    
                    $sql = "UPDATE send_delay_mail set fire=:fire, isFired:=0
                        WHERE from_mail=:from";
                    if($stmt = $this->_db->prepare($sql)) {
                        $stmt->bindParam(":from", $from, PDO::PARAM_STR);
                        $stmt->bindParam(":fire", $fire, PDO::PARAM_STR);
                        $stmt->execute();
                        $stmt->closeCursor();
            
                        $userID = $this->_db->lastInsertId();
                        $succeed = dechex($userID);
                    } else {
                        echo "error in Updating send_delay_mail table for first mail fire insert";
                    }
                }
            } else {
                //update time and message_id
                    $from = $To;
                    $message_id = $mail['message_id']+1;
                    $count = $message_id;
                    $campaign_id = $mail['campaign_id'];
                    $next_fire = $this->getDelayFireTimeDiff($campaign_id, $message_id);
                    $fire = date("Y-m-d H:i:s", strtotime("+{$next_fire} minutes", time()));
                    
                    $sql = "UPDATE send_delay_mail SET message_id=:message_id, count=:count, fire=:fire, isFired:=0
                        WHERE from_mail=:from";
                    if($stmt = $this->_db->prepare($sql)) {
                        $stmt->bindParam(":from", $from, PDO::PARAM_STR);
                        $stmt->bindParam(":fire", $fire, PDO::PARAM_STR);
                        $stmt->bindParam(":message_id", $message_id, PDO::PARAM_INT);
                        $stmt->bindParam(":count", $count, PDO::PARAM_INT);
                        $stmt->execute();
                        $stmt->closeCursor();
            
                        $userID = $this->_db->lastInsertId();
                        $succeed = dechex($userID);
                    } else {
                        echo "error in Updating send_delay_mail table for first mail fire insert";
                    }
            }
            
            }
        }
    }
    
	public function getDelayFireTimeDiff($campaign_id, $delay_message_id){
	   $sql = "SELECT delay
                FROM delay_message WHERE campaign_id=:campaign_id AND delay_message_id=:delay_message_id";
        if($stmt = $this->_db->prepare($sql)) {
			$stmt->bindParam(":campaign_id", $campaign_id, PDO::PARAM_INT);
			$stmt->bindParam(":delay_message_id", $delay_message_id, PDO::PARAM_INT);
            $stmt->execute();
    		$row = $stmt->fetch();
			if(isset($row['delay'])) return $row['delay'];
			else return 0;
		}
		else
		{
        	echo "NO delay time found for a campaing and a message";
			return 0;
        }
   }
	
	public function isItFirstDelay($mail_id){
		$sql = "SELECT from_mail FROM send_delay_mail 
			WHERE from_mail=:mail_id LIMIT 1 ";
        if($stmt = $this->_db->prepare($sql)) {
			$stmt->bindParam(":mail_id", $mail_id, PDO::PARAM_STR);
            $stmt->execute();
            if($stmt->rowCount()==0) 
			{
				return true;
            }
			else
			{
				return false;
			}
		}
		return false;
	}

}
?>
